with t_network_base as (
    select *
         , split(path, '/')[1] as path_1
         , split(path, '/')[2] as path_2
         , split(path, '/')[3] as path_3
         , split(path, '/')[4] as path_4
         , split(path, '/')[5] as path_5
         , split(path, '/')[6] as path_6
         , case
               when type_id = 22 then 1
               when type_id = 334 then 2
               when type_id = 335 and (is_first_franchisee = 1 or is_second_franchisee = 1) then 3
               when type_id = 335 and is_distribution_center = 1 then 4
               when type_id = 336 and is_entrepot = 1 then 5
               when type_id = 336 and is_entrepot = 2 then 6
        end                    as network_type --网点类型:1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点
    from jms_ods.yl_lmdm_sys_network_hf --网点资料 --网点资料
    where dt = '{{ execution_date | cst_hour }}'
      and is_delete = 1
      and code not like '%test%'
      and name not like '%test%'
      and code not like '%Test%'
      and code not like '%测试%'
      and name not like '%测试%'
      and code not like '%suger%'
      and name not like '%验证%'
      and code not like '%text%'
      and name not like '%text%'
      and if(type_id = 334 and length(code) = 6 and substr(code,1,4) = 'XNDL',1,0) = 0
      and if(type_id = 335 and is_distribution_center = 1 and length(code) = 6 and substr(code,1,2) = 'XN',1,0) = 0
      and if(type_id = 335 and (is_first_franchisee = 1 or is_second_franchisee = 1) and length(code) = 9 and substr(code,1,4) = 'XNJM',1,0) = 0
      and if(type_id = 336 and is_entrepot = 2 and length(code) = 7 and substr(code,1,2) = 'XN',1,0) = 0
      --    代理区层级：XNDL+2位递增码，例如：XNDL01
      --    转运中心层级：XN+4位递增码，例如：XN0001
      --    加盟商层级：XNJM+5位递增码，例如：XNJM00001
      --    网点层级：XN+5位递增码，例如：XN00001
)

insert
overwrite
table
jms_dim.dim_sys_network_detail_hi
select
id
, code
, name
, city_id
, city_desc
, area_id
, area_desc
, type_id
, type_desc
, is_delete
, create_time
, update_time
, is_entrepot
, opening_time
,regional_id
,regional_desc
,area_info_id
,area_info_desc
,provider_id
,provider_desc
from
t_network_base;